﻿
/*
 * THIS CLASS IS TO BE IMPLEMENTED IN THE FINAL SOLUTION
 * 
 * You have to open a data connection to QDF.mdf before you can run
 * or work on this. The QDF.mdf file is in the project folder, just keep it where it is.
 * When you open it, you will be asked if you want it to move the file to another
 * folder. Say no.
 * 
 * These are all the methods that can be called to work with the database
 * 
 * 
 * The methods available to call are below, (please look at actual method for parameters)
 * GetProfessor
 * GetAllProfessor
 * NewProfessor
 * UpdateProfessor
 * DeleteProfessor
 * 
 * GetQuestion
 * GetAllQuestion
 * NewQuestion
 * UpdateQuestion
 * DeleteQuestion
 * 
 * GetGameSet
 * GetAllGameSet
 * QuestionToGameset
 * QuestionFromGameset
 * RemoveFromGameset
 * GetAllIds
 * NewGameSet
 * UpdateGameSet
 * DeleteGameSetRecord
 * 
 * GetCategory
 * GetAllCategory
 * GetCategoryForSubCategory
 * NewCategory
 * UpdateCategory
 * DeleteCategory
 * 
 * GetSubCategory
 * GetAllSubCategory
 * GetSubCategoryForCategory
 * NewSubCategory
 * UpdateSubCategory
 * DeleteSubCategory
*/


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.ComponentModel;
using System.Data;
//using System.Drawing;
using System.IO;


namespace quizzer_linq_3
{
    public class Quizzer_Data_Access
    {
        DataClasses1DataContext db = new DataClasses1DataContext();
        StringBuilder sb = new StringBuilder();
        string newResult;

        
        static void Main(string[] args)
        {
            Application.Run(new LoginScreen());
        }
        

        //this parses all of the returned objects into strings
        public string ParseResult(object result)
        {
            StringWriter sw = new StringWriter(sb);
            try
            {
                StringDumper.Write(result, 1, sw);
                newResult = (sb.ToString());
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message + ex.StackTrace);
            }
            finally
            {
                sw.Close();
                sb.Clear();
            }
            return newResult;
        }


        //this gets a professor based on the first and last name
        public string GetProfessor(string first, string last)
        {
            var profQuery =
                from p in db.Professors
                where p.LastName == last && p.FirstName == first
                select String.Format("{0}|{1}|{2}|{3}|{4}", p.ProfessorID, p.FirstName, p.LastName, p.Email, p.Password);
            ParseResult(profQuery);
            return newResult;
        }

        //this gets a professor based on the email -Jayson
        public string GetProfessor(string email)
        {
            var profQuery =
                from p in db.Professors
                where p.Email == email
                select String.Format("{0}|{1}|{2}|{3}|{4}", p.ProfessorID, p.FirstName, p.LastName, p.Email, p.Password);
            ParseResult(profQuery);
            return newResult;
        }


        //this returns all professors in the professor table
        public string GetAllProfessor()
        {
            var allProfQuery =
                from p in db.Professors
                select String.Format("{0}|{1}|{2}|{3}|{4}", p.ProfessorID, p.FirstName, p.LastName, p.Email, p.Password);
            ParseResult(allProfQuery);
            return newResult;
        }


        //this adds a new professor
        public void NewProfessor(string first, string last, string email, string password)
        {
            var newProf = new Professor {FirstName = first, LastName = last, Email = email, Password = password};
            db.Professors.InsertOnSubmit(newProf);
            db.SubmitChanges();
        }


        //this updates a professor based on their ID
        public void UpdateProfessor(int ID, string first, string last, string email, string password)
        {
            Professor professor = db.Professors.First(c => c.ProfessorID == ID);
            professor.FirstName = first;
            professor.LastName = last;
            professor.Email = email;
            professor.Password = password;
            db.SubmitChanges();
        }


        //this deletes a professor based on the first and last name
        public void DeleteProfessor(string first, string last)
        {
            Professor professor = db.Professors.First(c => c.LastName == last && c.FirstName == first);
            db.Professors.DeleteOnSubmit(professor);
            db.SubmitChanges();
        }


        //this deletes a professor based on the email -Jayson
        public void DeleteProfessor(string email)
        {
            Professor professor = db.Professors.First(c => c.Email == email);
            db.Professors.DeleteOnSubmit(professor);
            db.SubmitChanges();
        }


        //this gets a question based on the question ID
        public string GetQuestion(int id)
        {
            var qQuery =
                from p in db.Questions
                where p.QuestionID == id
                select String.Format("{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}", p.QuestionID, p.SubCategoryID, p.Topic, p.TheQuestion, p.DifficultyLevel, p.PublicAccess, p.Ans01, p.Ans02, p.Ans03, p.Ans04, p.Ans05, p.Rating, p.Feedback, p.ProfessorID);
            ParseResult(qQuery);
            return newResult;
        }


        //this gets all questions from the questions table
        public string GetAllQuestion()
        {
            var allQQuery =
                from p in db.Questions
                select String.Format("{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}", p.QuestionID, p.SubCategoryID, p.Topic, p.TheQuestion, p.DifficultyLevel, p.PublicAccess, p.Ans01, p.Ans02, p.Ans03, p.Ans04, p.Ans05, p.Rating, p.Feedback, p.ProfessorID);
            ParseResult(allQQuery);
            return newResult;
        }


        //this ads a new question to the question table
        public void NewQuestion(
            int subCatID, 
            string topic, 
            string question, 
            string dLevel, 
            string pAccess, 
            string ans01, 
            string ans02, 
            string ans03, 
            string ans04, 
            string ans05, 
            string rating, 
            string feedback, 
            int pID)
        {
            var newQ = new Question {
                SubCategoryID = subCatID, 
                Topic = topic, 
                TheQuestion = question, 
                DifficultyLevel = dLevel, 
                PublicAccess = pAccess, 
                Ans01 = ans01, 
                Ans02 = ans02, 
                Ans03 = ans03, 
                Ans04 = ans04, 
                Ans05 = ans05, 
                Rating = rating, 
                Feedback = feedback, 
                ProfessorID = pID 
            };
            db.Questions.InsertOnSubmit(newQ);
            db.SubmitChanges();
        }


        //This is a helper class that finds the last added question ID from the database
        public int getMaxQuestionID()
        {
            string[] questionHolder = GetAllQuestion().Split('\n');
            int maxQuestionID = 0;
            int placeholder = 0;
            for (int questionHolderCounter = 0; questionHolderCounter < questionHolder.Count()-1; questionHolderCounter++)
            {
                placeholder = int.Parse((string)((string)questionHolder.GetValue(questionHolderCounter)).Split('|').GetValue(0));
                if (placeholder > maxQuestionID)
                    maxQuestionID = placeholder;
            }
            return (maxQuestionID);
        }
        

        //this updates a question based on the questionID
        public void UpdateQuestion(int qID, int subCatID, string topic, string question, string dLevel, string pAccess, string ans01, string ans02, string ans03, string ans04, string ans05, string rating, string feedback, int pID)
        {
            Question upQuestion = db.Questions.First(c => c.QuestionID == qID);
            upQuestion.SubCategoryID = subCatID;
            upQuestion.Topic = topic;
            upQuestion.TheQuestion = question;
            upQuestion.DifficultyLevel = dLevel;
            upQuestion.PublicAccess = pAccess;
            upQuestion.Ans01 = ans01;
            upQuestion.Ans02 = ans02;
            upQuestion.Ans03 = ans03;
            upQuestion.Ans04 = ans04;
            upQuestion.Ans05 = ans05;
            upQuestion.Rating = rating;
            upQuestion.Feedback = feedback;
            upQuestion.ProfessorID = pID;
            db.SubmitChanges();
        }


        //this deletes a question based on the questionID
        public void DeleteQuestion(int qID)
        {
            Question question = db.Questions.First(c => c.QuestionID == qID);
            db.Questions.DeleteOnSubmit(question);
            db.SubmitChanges();
        }


        //this gets gameset records based on gameset name
        public string GetGameSet(string gName)
        {
            var gQuery =
                from g in db.GameSets
                where g.GameSetName == gName
                select String.Format("{0}|{1}", g.GameSetID, g.GameSetName);
            ParseResult(gQuery);
            return newResult;
        }


        //this returns all gameset table records
        public string GetAllGameSet()
        {
            var gQuery =
                from g in db.GameSets
                select String.Format("{0}|{1}", g.GameSetID, g.GameSetName);
            ParseResult(gQuery);
            return newResult;
        }
        

        //this creates a new record in the 'question_gameset' table and assigns a question to a gameset
        public void questionToGameset(int qID, int gsID)
        {
            var qtgs = new question_gameset { QuestionID = qID, GameSetID = gsID };
            db.question_gamesets.InsertOnSubmit(qtgs);
            db.SubmitChanges();
        }


        //this retrieves all questions from a single gameset from the 'question_gameset' table using the gamesetID
        public string questionsFromGameset(int gsID)
        {
            var qgQuery =
                from g in db.question_gamesets
                where g.GameSetID == gsID
                select String.Format("{0}", g.QuestionID);
            ParseResult(qgQuery);
            return newResult;
        }

        //this retrieves all gamesets with a passed question id from the 'question_gameset' table -Jayson
        public string gameSetWithQuestion(int qID)
        {
            var qgQuery =
                from q in db.question_gamesets
                where q.QuestionID == qID
                select String.Format("{0}", q.GameSetID);
            ParseResult(qgQuery);
            return newResult;
        }

        //this removes a single question from a gameset from the 'question_gameset' table using the questionID
        public void removeFromGameset(int qID, int gsID)
        {
            question_gameset qgs = db.question_gamesets.First(c => c.QuestionID == qID && c.GameSetID == gsID);
            db.question_gamesets.DeleteOnSubmit(qgs);
            db.SubmitChanges();
        }


        //this gets all questionID's by a single professor using the professorID
        public string getAllIds(int pID)
        {
            var qIDQuery =
                from g in db.Questions
                where g.ProfessorID == pID
                select String.Format("{0}", g.QuestionID);
            ParseResult(qIDQuery);
            return newResult;
        }


        //this adds a new a new gameset record
        public void NewGameSet(string gName)
        {
            var newSet = new GameSet {GameSetName = gName};
            db.GameSets.InsertOnSubmit(newSet);
            db.SubmitChanges();
        }

        //this updates a gameset based on the gameset name
        public void UpdateGameSet(string gName)
        {
            GameSet gSet = db.GameSets.First(c => c.GameSetName == gName);
            gSet.GameSetName = gName;
            db.SubmitChanges();
        }


        //this deletes a GameSet record based on Gameset ID
        public void DeleteGameSetRecord(int ID)
        {
            GameSet gSet = db.GameSets.First(c => c.GameSetID == ID);
            db.GameSets.DeleteOnSubmit(gSet);
            db.SubmitChanges();
        }


        //this gets category records based on category name
        public string GetCategory(string cName)
        {
            var cQuery =
                from c in db.Categories
                where c.CategoryName == cName
                select String.Format("{0}|{1}", c.CategoryID, c.CategoryName);
            ParseResult(cQuery);
            return newResult;
        }


        //this returns all category data
        public string GetAllCategory()
        {
            var catQuery =
                from c in db.Categories
                select String.Format("{0}|{1}", c.CategoryID, c.CategoryName);
            ParseResult(catQuery);
            return newResult;
        }


        //This returns the category given a subcategory -Jayson
        public string GetCategoryForSubCategory(int subCategory)
        {
            var categoryQuery =
                from cat in db.Categories
                join sub in db.SubCategories on cat.CategoryID equals sub.CategoryID
                where sub.SubCategoryID == subCategory
                select String.Format("{0}|{1}|{2}", sub.SubCategoryID, sub.SubCategoryName, sub.CategoryID);
            ParseResult(categoryQuery);
            return newResult;
        }


        //this adds a new a new category
        public void NewCategory(string catName)
        {
            var newCat = new Category { CategoryName = catName};
            db.Categories.InsertOnSubmit(newCat);
            db.SubmitChanges();
        }


        //this updates a category record based on the category name
        public void UpdateCategory(string catName)
        {
            Category uCat = db.Categories.First(c => c.CategoryName == catName);
            uCat.CategoryName = catName;
            db.SubmitChanges();
        }


        //this deletes a category record based on the category name
        public void DeleteCategory(string catName)
        {
            Category dCat = db.Categories.First(c => c.CategoryName == catName);
            db.Categories.DeleteOnSubmit(dCat);
            db.SubmitChanges();
        }


        //this gets SUBcategory records based on SUBcategory name
        public string GetSubCategory(string subName)
        {
            var subQuery =
                from s in db.SubCategories
                where s.SubCategoryName == subName
                select String.Format("{0}|{1}|{2}", s.SubCategoryID, s.SubCategoryName, s.CategoryID);
            ParseResult(subQuery);
            return newResult;
        }


        //this returns all SUBcategory data
        public string GetAllSubCategory()
        {
            var catSubQuery =
                from s in db.SubCategories
                select String.Format("{0}|{1}|{2}", s.SubCategoryID, s.SubCategoryName, s.CategoryID);
            ParseResult(catSubQuery);
            return newResult;
        }

        //This returns all subcategories for a given category -Jayson
        public string GetSubCategoryForCategory(int category)
        {
            var subCategoryQuery =
                from sub in db.SubCategories
                join cat in db.Categories on sub.CategoryID equals cat.CategoryID
                where sub.CategoryID == category
                select String.Format("{0}|{1}|{2}", sub.SubCategoryID, sub.SubCategoryName, sub.CategoryID);
            ParseResult(subCategoryQuery);
            return newResult;
        }

        //this adds a new a new SUBcategory record
        public void NewSubCategory(string subName, int catID)
        {
            var newSub = new SubCategory { SubCategoryName = subName, CategoryID = catID };
            db.SubCategories.InsertOnSubmit(newSub);
            db.SubmitChanges();
        }


        //this updates a SUBcategory record based on the category name
        public void UpdateSubCategory(string subName, int catID)
        {
            SubCategory sCat = db.SubCategories.First(c => c.SubCategoryName == subName);
            sCat.SubCategoryName = subName;
            sCat.CategoryID = catID;
            db.SubmitChanges();
        }


        //this deletes a SUBcategory record based on the category name
        public void DeleteSubCategory(string subName)
        {
            SubCategory sCat = db.SubCategories.First(c => c.SubCategoryName == subName);
            db.SubCategories.DeleteOnSubmit(sCat);
            db.SubmitChanges();
        }
        
        //this deletes a SUBcategory record based on the subcategory id
        public void DeleteSubCategory(int subID)
        {
            SubCategory sCat = db.SubCategories.First(c => c.SubCategoryID == subID);
            db.SubCategories.DeleteOnSubmit(sCat);
            db.SubmitChanges();
        }
    }
}